<?php
/**
 * Created by IDEA.
 * User: dinesh
 * Date: Jul 17, 2013
 * Time: 11:37:35 AM
 * To change this template use File | Settings | File Templates.
 */

class TldCard {
    private $db;
    private $id;
    private $code;
    private $lotId;
    private $validTld = array(0);
    private $validIssueTldCard = array(0);
    private $instituteMenu;
    private $not_Return = CardStatus::NOT_RETURNED;
    private $processing = CardStatus::PROCESSING;
    private $issued = CardStatus::ISSUED;
    private $invalid = CardStatus::INVALID;
    private $available = CardStatus::AVAILABLE;
    private $months = array(
        1 => 'January',
        2 => 'February',
        3 => 'March',
        4 => 'April',
        5 => 'May',
        6 => 'June',
        7 => 'July',
        8 => 'August',
        9 => 'September',
        10 => 'October',
        11 => 'November',
        12 => 'December');


    public function __construct() {
        $ado = new AdodbDriver();
        $this->db = $ado->pConnect(true);
        $this->db->debug = false;
        $this->setValidTldCardArray();
    }

    function __set($name, $value) {
        // TODO: Implement __set() method.
        $this->$name = $value;

    }

    function __get($name) {
        // TODO: Implement __get() method.
        return $this->$name;
    }

    public function setValidTldCardArray() {
        $sql = "SELECT code from aea_tld Where is_pass_test=1 AND is_qa_card=0 order by id asc";
        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        $key = 0;
        while (!$resAll->EOF) {
            $this->validTld[$key] = $resAll->fields['code'];
            $key++;
            $resAll->MoveNext();

        }

    }

    public function setInstituteArray() {
        $sql = "SELECT id,name from aea_institute order by id asc";
        $dep_code = $this->db->Execute($sql);
        $this->instituteMenu = $dep_code->GetAssoc();

    }

    public function setValidIssueTldCardArray() {
        $sql = "SELECT code from aea_tld Where is_pass_test=1 AND is_qa_card=0 AND status=0 order by id asc";
        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        $key = 0;
        while (!$resAll->EOF) {
            $this->validIssueTldCard[$key] = $resAll->fields['code'];
            $key++;
            $resAll->MoveNext();

        }


    }

    public function validateIssueCard($id, $cards, $remove, $bgCard) {
        $isValid = true;
        $msg = "Invalid Card ID's ";
        foreach ($id as $value) {
            if ($remove[$value] != 1) {
                if (!in_array($cards[$value], $this->validIssueTldCard)) {
                    $msg .= ": " . $cards[$value];
                    $isValid = false;
                }
            }

        }
        if (!in_array($bgCard, $this->validIssueTldCard)) {
            $msg .= ": " . $bgCard;
            $isValid = false;
        }
        return array('msg' => $msg, 'valid' => $isValid);
    }

    public function validateIssueCardEdit($id, $cards, $remove) {
        $isValid = true;
        $msg = "Invalid Card ID's ";
        foreach ($id as $value) {
            if ($remove[$value] != 1) {
                if (!in_array($cards[$value], $this->validIssueTldCard)) {
                    $msg .= ": " . $cards[$value];
                    $isValid = false;
                }
            }

        }

        return array('msg' => $msg, 'valid' => $isValid);
    }

    public function validateDoseValue($doseValue) {
        $isValid = true;
        $msg = "Invalid Dose values:  ";
        foreach ($doseValue as $value) {
            if (!is_numeric($value)) {
                $msg .= ": " . $value;
                $isValid = false;
            }

        }

        return array('msg' => $msg, 'valid' => $isValid);
    }

    public function validateTldCard($code) {
        $sql = "SELECT  id FROM aea_tld Where code= '$code' AND is_delete=0 LIMIT 1";
        $recordSet = $this->db->Execute($sql);
        return $recordSet->FetchRow();
    }

    public function validateMonthYear($instituteId, $sectionId, $month, $year) {
        $sql = "SELECT  id FROM aea_distribution_list
                Where institute_id= '$instituteId'
                AND section_id= '$sectionId'
                AND year= '$year'
                AND month= '$month' 
                AND is_delete=0 LIMIT 1";
        $recordSet = $this->db->Execute($sql);
        return $recordSet->FetchRow();
    }

    public function updateCard($id, $passTest, $qaCard, $br) {
        ADOdb_Active_Record::SetDatabaseAdapter($this->db);
        try {
            $card = new TldCardAR();
            $update = false;
            foreach ($id as $value) {
                $this->id = $value;
                if ($this->id != '' AND $this->id != NULL) {
                    $card->Load("id=$this->id");
                    $card->update_user_id = $_SESSION['user_id'];
                    $card->is_pass_test = $passTest[$value] == 1 ? 1 : 0;
                    $card->is_broken = $br[$value] == 1 ? 1 : 0;
                    if ($br[$value] == 1) {
                        $card->status = -1;
                    }
                    $card->is_qa_card = $qaCard[$value] == 1 ? 1 : 0;
                    $j = $card->save();
                    if ($j == 1) {
                        $update = true;
                    }
                }
            }

        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());

        }

        if ($update) {
            $i = 1;
        } else {
            $i = -1;
        }

        return $i;
    }

    public function updateEmpCardList($id, $list1, $list2, $instituteID, $bgCard1, $bgCard2) {
        //print_r($this->validTld)

        ADOdb_Active_Record::SetDatabaseAdapter($this->db);
        try {
            $card = new EmpCardListAR();
            $update = false;
            foreach ($id as $value) {
                $this->id = $value;
                //check validity of tld code
                if (!in_array($list1[$value], $this->validTld)) {
                    return $list1[$value];
                    exit;
                }
                if (!in_array($list2[$value], $this->validTld)) {
                    return $list2[$value];
                    exit;
                }
                if ($this->id != '' AND $this->id != NULL) {
                    $card->Load("id=$this->id");
                    $card->update_user_id = $_SESSION['user_id'];
                    $card->list1_card_id = $list1[$value];
                    $card->list2_card_id = $list2[$value];

                    $j = $card->save();
                    if ($j == 1) {
                        $update = true;
                    }
                }
            }
            if ($instituteID > 0) {
                $sql = "UPDATE aea_institute SET bg_card1='$bgCard1', bg_card2='$bgCard2' WHERE id = '$instituteID'";
                $this->db->Execute($sql);

            }

        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());

        }

        if ($update) {
            $i = 1;
        } else {
            $i = -1;
        }

        return $i;
    }

    public function createDistribution($id, $instituteId, $sectionId, $employId, $card, $notReturn, $returnDate, $year, $month, $remove, $tMonth, $tYear, $bgCard) {
        //print_r($this->validTld)

        //  $insertId = $this->db->getOne("INSERT INTO aea_distribution_list (institute_id,section_id,month,year,send_date,receive_date,) values ('test') RETURNING id");


        ADOdb_Active_Record::SetDatabaseAdapter($this->db);
        try {
            $dList = new DistributionListAR();
            $dList->create_time = date('Y-m-d H:i:s', time());
            $dList->create_user_id = $_SESSION['user_id'];
            $dList->month = $month;
            $dList->year = $year;
            $dList->to_month = $tMonth;
            $dList->to_year = $tYear;
            $dList->institute_id = $instituteId;
            $dList->section_id = $sectionId;
            $dList->receive_date = $returnDate;
            $dList->send_date = date('Y-m-d', time());
            $dList->is_delete = 0;
            $dList->status = 0;
            $i = $dList->save();
            $disId = $dList->id;

            //calculate period of the distribution
            $_fromDate = $year . "-" . $month . "-01";
            $_toDateTemp = $tYear . "-" . $tMonth . "-01";
            $_toMonth = new DateTime($_toDateTemp);
            $_lastDay = $_toMonth->format('t');
            $_toDate = $tYear . "-" . $tMonth . "-" . $_lastDay;
            foreach ($id as $value) {
                if (!$remove[$value] == 1) { //Ignore the removed TLD when RM checkbox is tick
                    $cardAR = new DistributionCardAR();
                    $cardAR->employ_id = $value;
                    $cardAR->card_id = $card[$value];
                    $cardAR->list_id = $disId;
                    $cardAR->received_date = $returnDate;
                    $cardAR->from_date = $_fromDate;
                    $cardAR->to_date = $_toDate;
                    $cardAR->status = $this->issued;
                    $cardAR->create_time = date('Y-m-d H:i:s', time());
                    $cardAR->create_user_id = $_SESSION['user_id'];
                    $cardAR->update_user_id = $_SESSION['user_id'];
                    $cardAR->save();
                    // $cardAR->__destruct();
                    unset($cardAR);
                    //update status of the card in global table
                    $sql = "UPDATE aea_tld SET status='$this->issued',current_dis_list='$disId' WHERE code='$card[$value]'";
                    $resAll = $this->db->Execute($sql);
                }


            }
            //Save Background card
            $cardAR = new DistributionCardAR();
            $cardAR->employ_id = 0; //no employee assign for background card
            $cardAR->card_id = $bgCard;
            $cardAR->list_id = $disId;
            $cardAR->received_date = $returnDate;
            $cardAR->from_date = $_fromDate;
            $cardAR->to_date = $_toDate;
            $cardAR->status = $this->issued;
            $cardAR->create_time = date('Y-m-d H:i:s', time());
            $cardAR->create_user_id = $_SESSION['user_id'];
            $cardAR->update_user_id = $_SESSION['user_id'];
            $cardAR->save();
            // $cardAR->__destruct();
            unset($cardAR);
            $sql = "UPDATE aea_tld SET status='$this->issued', current_dis_list='$disId' WHERE code='$bgCard'";
            $this->db->Execute($sql);

        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $i;
    }

    public function createDistributionEdit($id, $instituteId, $sectionId, $employId, $card, $notReturn, $returnDate, $year, $month, $remove, $tMonth, $tYear, $bgCard, $listId) {
        //print_r($this->validTld)

        //  $insertId = $this->db->getOne("INSERT INTO aea_distribution_list (institute_id,section_id,month,year,send_date,receive_date,) values ('test') RETURNING id");


        ADOdb_Active_Record::SetDatabaseAdapter($this->db);
        try {
            $dList = new DistributionListAR();
            $dList->Load("id=$listId");
            $disId = $dList->id;
            //calculate period of the distribution
            $_fromDate = $year . "-" . $month . "-01";
            $_toDateTemp = $tYear . "-" . $tMonth . "-01";
            $_toMonth = new DateTime($_toDateTemp);
            $_lastDay = $_toMonth->format('t');
            $_toDate = $tYear . "-" . $tMonth . "-" . $_lastDay;
            foreach ($id as $value) {
                if (!$remove[$value] == 1) { //Ignore the removed TLD when RM checkbox is tick
                    $cardAR = new DistributionCardAR();
                    $cardAR->employ_id = $value;
                    $cardAR->card_id = $card[$value];
                    $cardAR->list_id = $disId;
                    $cardAR->received_date = $returnDate;
                    $cardAR->from_date = $_fromDate;
                    $cardAR->to_date = $_toDate;
                    $cardAR->status = $this->issued;
                    $cardAR->create_time = date('Y-m-d H:i:s', time());
                    $cardAR->create_user_id = $_SESSION['user_id'];
                    $cardAR->update_user_id = $_SESSION['user_id'];
                    $i = $cardAR->save();
                    // $cardAR->__destruct();
                    unset($cardAR);
                    //update status of the card in global table
                    $sql = "UPDATE aea_tld SET status='$this->issued',current_dis_list='$disId' WHERE code='$card[$value]'";
                    $resAll = $this->db->Execute($sql);
                }


            }


        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $i;
    }

    public function updateDistribution($id, $card, $notReturn, $returnDate, $fromDate, $toDate) {

        try {

            foreach ($id as $value) {
                if (!$notReturn[$value] == 1) { //Ignore the removed TLD when RM checkbox is tick
                    $sql = "UPDATE aea_distribution_card SET from_date='$fromDate[$value]',to_date='$toDate[$value]',received_date='$returnDate[$value]',status='$this->processing' WHERE id='$value'";
                    $this->db->Execute($sql);
                    //update status of the card in global table
                    $sql = "UPDATE aea_tld SET status='$this->processing' WHERE code='$card[$value]'";
                    $resAll = $this->db->Execute($sql);
                }
                if ($notReturn[$value] == 1) {
                    $sql = "UPDATE aea_tld SET status='$this->not_Return' WHERE code='$card[$value]'";
                    $resAll = $this->db->Execute($sql);
                    $sql = "UPDATE aea_distribution_card SET status='$this->not_Return' WHERE id='$value'";
                    $this->db->Execute($sql);
                }
            }

        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $resAll;
    }

    public function updateDistributionCard($id, $card, $returnDate, $fromDate, $toDate) {

        try {

            $sql = "UPDATE aea_distribution_card SET from_date='$fromDate',to_date='$toDate',received_date='$returnDate',status='$this->processing' WHERE id='$id'";
            $this->db->Execute($sql);
            //update status of the card in global table
            $sql = "UPDATE aea_tld SET status='$this->processing' WHERE code='$card'";
            $resAll = $this->db->Execute($sql);


        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $resAll;
    }

    public function updateDistributionWithDose($id, $card, $dose, $skip, $fromDate, $toDate) {

        try {

            foreach ($id as $value) {
                if (!$skip[$value] == 1) {
                    $sql = "UPDATE aea_distribution_card SET from_date='$fromDate[$value]',to_date='$toDate[$value]',dose='$dose[$value]',status='$this->available' WHERE id='$value'";
                    $this->db->Execute($sql);
                    //update status of the card in global table
                    $sql = "UPDATE aea_tld SET status='$this->available' WHERE code='$card[$value]'";
                    $resAll = $this->db->Execute($sql);
                }
            }


        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $resAll;
    }

    public function updateDistributionWithDoseManually($id, $dose, $card) {

        try {
            $sql = "UPDATE aea_distribution_card SET dose='$dose' ,status='$this->available' WHERE id='$id'";
            $this->db->Execute($sql);
            //update status of the card in global table
            $sql = "UPDATE aea_tld SET status='$this->available' WHERE code='$card'";
            $resAll = $this->db->Execute($sql);


        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $resAll;
    }

    function save() {
        ADOdb_Active_Record::SetDatabaseAdapter($this->db);
        try {
            $card = new TldCardAR();
            if ($this->id != '' AND $this->id != NULL) {
                $card->Load("id=$this->id");
            } else {
                $card->create_time = date('Y-m-d H:i:s', time());
                $card->create_user_id = $_SESSION['user_id'];
                $card->is_pass_test = 0;
                $card->is_dosimeter = 0;
                $card->is_qa_card = 0;
                $card->is_delete = 0;
                $card->status = 0;
                $card->current_dis_list = 0;
                $card->is_broken = 0;

            }
            $card->lot_id = $this->lotId;
            $card->code = $this->code;
            $i = $card->save();
        } catch (exception $e) {
            //var_dump($e);
            print_r($e);
            adodb_backtrace($e->gettrace());
        }
        return $i;
    }

    public function getTldCardGrid($lotId, $code) {

        $sql = "SELECT  * FROM aea_tld Where is_delete='0'";
        if ($lotId != '')
            $sql .= " and lot_id = '$lotId'";
        if ($code != '')
            $sql .= " and code like '$code%'";

        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $count = 1;
            $innerHtml = "<table class='gradienttable'><tr><th width='50px'>id</th><th width='150px'>Code</th><th width='150px'>Pass Performance Test</th><th width='150px'>QC Card </th> <th width='150px'>Removed Card </th></tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['id'] . "' />
                    <td>" . $count . "</td>";
                if ($resAll->fields['status'] == CardStatus::AVAILABLE) {
                    // valid tld card
                    $innerHtml .= "<td style='background:#7cfc00;'>" . $resAll->fields['code'] . "</td>";

                } elseif ($resAll->fields['status'] == CardStatus::NOT_RETURNED) {
                    // not return TLD card
                    $innerHtml .= "<td style='background:#ff00ff;'>" . $resAll->fields['code'] . "</td>";

                } elseif ($resAll->fields['status'] == CardStatus::ISSUED) {
                    // issued tld card
                    $innerHtml .= "<td style='background:#FFFF00;'>" . $resAll->fields['code'] . "</td>";

                } elseif ($resAll->fields['status'] == CardStatus::PROCESSING) {
                    // processing tld card
                    $innerHtml .= "<td style='background:#7878E6;'>" . $resAll->fields['code'] . "</td>";

                } elseif ($resAll->fields['status'] == CardStatus::INVALID) {
                    // invalid tld card
                    $innerHtml .= "<td style='background:#ff0000;'>" . $resAll->fields['code'] . "</td>";

                }


                $innerHtml .= "<td align='center'><input type='checkbox'  value='1' id='performanceTest" . $resAll->fields['id'] . "' name='performanceTest[" . $resAll->fields['id'] . "]' ";
                if ($resAll->fields['is_pass_test'] == 1) {
                    $innerHtml .= " checked";
                }
                $innerHtml .= "  > </td>
                   <td align='center'><input type='checkbox' value='1' id='qaCard" . $resAll->fields['id'] . "' name='qa[" . $resAll->fields['id'] . "]' ";
                if ($resAll->fields['is_qa_card'] == 1) {
                    $innerHtml .= " checked";
                }
                $innerHtml .= "  > </td>
                   <td align='center'><input type='checkbox' value='1' id='br" . $resAll->fields['id'] . "' name='br[" . $resAll->fields['id'] . "]' ";
                if ($resAll->fields['is_broken'] == 1) {
                    $innerHtml .= " checked";
                }
                $innerHtml .= " > </td>
                   </tr>";
                $count++;
                $resAll->MoveNext();
            }
            $innerHtml .= "</table> <input type='hidden' id='total'  name='total' value='" . $totalRows . "' />";

        } else {
            $innerHtml = "No Records Found !";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }

    public function getEmpTldCardList($instituteId, $sectionId) {

        $sql = "SELECT  aea_employ_institute.id as ID,full_name,aea_profile.institute_id,list1_card_id,list2_card_id FROM aea_employ_institute, aea_profile Where aea_profile.id=aea_employ_institute.emp_id AND aea_profile.is_delete='0' and to_date='0000-00-00'";
        if ($instituteId > 0)
            $sql .= " and aea_employ_institute.institute_id = '$instituteId'";
        if ($sectionId > 0)
            $sql .= " and aea_employ_institute.section_id = '$sectionId'";

        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $innerHtml = "<table class='gradienttable'><tr><th width='50px'>id</th><th width='350px'>Name</th><th width='150px'>List 1</th><th width='150px'>List 2 </th> </tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['ID'] . "' />
                    <td>" . $resAll->fields['ID'] . "</td>
                   <td>" . $resAll->fields['full_name'] . "</td>
                   <td align='center'><input type='text' onblur='validateCode(this)' value='" . $resAll->fields['list1_card_id'] . "' id='list1" . $resAll->fields['ID'] . "' name='list1[" . $resAll->fields['ID'] . "]'></td>
                   <td align='center'><input type='text' onblur='validateCode(this)' value='" . $resAll->fields['list2_card_id'] . "' id='list2" . $resAll->fields['ID'] . "' name='list2[" . $resAll->fields['ID'] . "]' > </td></tr>";
                $resAll->MoveNext();
            }
            if ($instituteId != 0) {
                if ($sectionId != 0) {
                    $sql = "SELECT  bg_card1,bg_card2 FROM aea_institute  where id= '$sectionId'";
                } else {
                    $sql = "SELECT  bg_card1,bg_card2 FROM aea_institute  where id= '$instituteId'";
                }
                $recode = $this->db->Execute($sql);
                $rows = $recode->RecordCount();
                if ($rows > 0) {
                    while (!$recode->EOF) {
                        $innerHtml .= "<tr>
                                    <td> </td>
                                    <td>Background Cards</td>
                                    <td align='center'><input type='text' onblur='validateCode(this)' value='" . $recode->fields['bg_card1'] . "' id='bg_card1' name='bg_card1'></td>
                                    <td align='center'><input type='text' onblur='validateCode(this)' value='" . $recode->fields['bg_card2'] . "' id='bg_card2' name='bg_card2' > </td></tr>";
                        $recode->MoveNext();
                    }

                }
            }

            $innerHtml .= "</table> <input type='hidden' id='total'  name='total' value='" . $totalRows . "' />";

        } else {
            $innerHtml = "No Records Found !";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }

    public function getCardInfoForDose($cardId) {

        $sql = "SELECT current_dis_list, status, lot_id FROM aea_tld WHERE code ='$cardId' LIMIT 1";
        $record = $this->db->Execute($sql);
        $totalRows = $record->RecordCount();

        if ($totalRows == 0) {
            $innerHtml = "<span style='color:red;font-size:25px;'>No Record found !  Card ID " . $cardId . " is invalid!</span>";
        } else {

            $currentDisId = $record->fields['current_dis_list'];
            $sql = "SELECT aea_distribution_card.id AS ID, dose, employ_id, received_date,from_date,to_date, aea_distribution_list.institute_id AS ins_id, aea_distribution_list.section_id AS sec_id,
                        month , year, to_month, to_year,send_date,full_name
                        FROM aea_distribution_card
                        JOIN aea_distribution_list ON aea_distribution_card.list_id = aea_distribution_list.id
                        LEFT JOIN aea_profile ON aea_distribution_card.employ_id = aea_profile.id
                        WHERE list_id ='$currentDisId'
                        AND card_id ='$cardId'
                        LIMIT 1";
            $recordDisCard = $this->db->Execute($sql);
            $totalRows2 = $recordDisCard->RecordCount();
            if ($totalRows2 > 0) {

                $this->setInstituteArray();
                $innerHtml .= "<table><tr><td width=500px>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr>";
                if ($recordDisCard->fields['employ_id'] == 0) {
                    $innerHtml .= "<td colspan=2 align=center>Background Card :" . $cardId . "</td>";

                } else {
                    $innerHtml .= "<td width=150px>Employee Name  </td>";
                    $innerHtml .= "<td width=350px> " . $recordDisCard->fields['full_name'] . "</td>";
                }
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td width=150px>Institute Name  </td>";
                $innerHtml .= "<td width=350px> " . $this->instituteMenu[$recordDisCard->fields['ins_id']] . "</td>";
                $innerHtml .= "</tr>";
                if ($recordDisCard->fields['sec_id'] != 0) {
                    $innerHtml .= "<tr>";
                    $innerHtml .= "<td>Section Name  </td>";
                    $innerHtml .= "<td> " . $this->instituteMenu[$recordDisCard->fields['sec_id']] . "</td>";
                    $innerHtml .= "</tr>";
                }
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>Sent Date  </td>";
                $innerHtml .= "<td> " . $recordDisCard->fields['send_date'] . "</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";

                $innerHtml .= "</td><td>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<th colspan=2>Period  </th>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td width=100px>From  </td>";
                $innerHtml .= "<td width=200px>  " . $this->months[$recordDisCard->fields['month']] . " " . $recordDisCard->fields['year'] . " (" . $recordDisCard->fields['from_date'] . ")</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>To  </td>";
                $innerHtml .= "<td>  " . $this->months[$recordDisCard->fields['to_month']] . " " . $recordDisCard->fields['to_year'] . " (" . $recordDisCard->fields['to_date'] . ")</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>Received Date  </td>";
                $innerHtml .= "<td>  " . $recordDisCard->fields['received_date'] . "</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";
                $innerHtml .= "</td></tr></table>";
            }
            if ($record->fields['status'] == $this->processing) {
                $innerHtml .= "";
                $innerHtml .= "<table>";
                $innerHtml .= "<tr><input id='id' value='" . $recordDisCard->fields['ID'] . "' name='id' type='hidden' size='40'/><input id='card' value='" . $cardId . "' name='card' type='hidden' size='40'/>";
                $innerHtml .= "<td><label>Dose Value (&micro;Sv)</label></td>";
                $innerHtml .= "<td><input id='doseValue' value='" . $recordDisCard->fields['dose'] . "' name='doseValue' type='text' size='40'/> <span id='doseValueInfo'></span></td>";
                $innerHtml .= "<td><button id='btnLoad' onblur='validateDoseValue();' onclick='addDoseValue();' privilege='' class='clsButtonSmall'>Add Dose</button></td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";
                $innerHtml .= "<div style='margin-top:10px;'></div>";
            } else {
                if ($record->fields['status'] == $this->available) {
                    $innerHtml .= " <span style='background:#7cfc00;font-size:20px; margin-left:250px;'>Card status is Available</span>";
                } elseif ($record->fields['status'] == $this->issued) {
                    $innerHtml .= " <span style='background:#FFFF00;font-size:20px; margin-left:250px; '>Card status is Issued</span>";
                } elseif ($record->fields['status'] == $this->invalid) {
                    $innerHtml .= " <span style='background:#ff0000;font-size:20px;margin-left:250px; '>Card status is Invalid</span>";
                } elseif ($record->fields['status'] == $this->not_Return) {
                    $innerHtml .= " <span style='background:#ff00ff;font-size:20px; margin-left:250px;'>Card status is Not Return</span>";
                }


            }


        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }

    public function getCardInfoForCollect($cardId) {

        $sql = "SELECT current_dis_list, status, lot_id FROM aea_tld WHERE code ='$cardId' LIMIT 1";
        $record = $this->db->Execute($sql);
        $totalRows = $record->RecordCount();

        if ($totalRows == 0) {
            $innerHtml = "<span style='color:red;font-size:25px;'>No Record found !  Card ID " . $cardId . " is invalid!</span>";
        } else {

            $currentDisId = $record->fields['current_dis_list'];
            $sql = "SELECT aea_distribution_card.id AS ID, dose, employ_id, received_date,from_date,to_date, aea_distribution_list.institute_id AS ins_id, aea_distribution_list.section_id AS sec_id,
                        month , year, to_month, to_year,send_date,full_name
                        FROM aea_distribution_card
                        JOIN aea_distribution_list ON aea_distribution_card.list_id = aea_distribution_list.id
                        LEFT JOIN aea_profile ON aea_distribution_card.employ_id = aea_profile.id
                        WHERE list_id ='$currentDisId'
                        AND card_id ='$cardId'
                        LIMIT 1";
            $recordDisCard = $this->db->Execute($sql);
            $totalRows2 = $recordDisCard->RecordCount();
            if ($totalRows2 > 0) {

                $this->setInstituteArray();
                $innerHtml .= "<table><tr><td width=500px>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr>";
                if ($recordDisCard->fields['employ_id'] == 0) {
                    $innerHtml .= "<td colspan=2 align=center>Background Card :" . $cardId . "</td>";

                } else {
                    $innerHtml .= "<td width=150px>Employee Name  </td>";
                    $innerHtml .= "<td width=350px> " . $recordDisCard->fields['full_name'] . "</td>";
                }
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td width=150px>Institute Name  </td>";
                $innerHtml .= "<td width=350px> " . $this->instituteMenu[$recordDisCard->fields['ins_id']] . "</td>";
                $innerHtml .= "</tr>";
                if ($recordDisCard->fields['sec_id'] != 0) {
                    $innerHtml .= "<tr>";
                    $innerHtml .= "<td>Section Name  </td>";
                    $innerHtml .= "<td> " . $this->instituteMenu[$recordDisCard->fields['sec_id']] . "</td>";
                    $innerHtml .= "</tr>";
                }
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>Sent Date  </td>";
                $innerHtml .= "<td> " . $recordDisCard->fields['send_date'] . "</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";

                $innerHtml .= "</td><td>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<th colspan=2>Period  </th>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td width=100px>From  </td>";
                $innerHtml .= "<td width=200px>  " . $this->months[$recordDisCard->fields['month']] . " " . $recordDisCard->fields['year'] . " (" . $recordDisCard->fields['from_date'] . ")</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>To  </td>";
                $innerHtml .= "<td>  " . $this->months[$recordDisCard->fields['to_month']] . " " . $recordDisCard->fields['to_year'] . " (" . $recordDisCard->fields['to_date'] . ")</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>Received Date  </td>";
                $innerHtml .= "<td>  " . $recordDisCard->fields['received_date'] . "</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";
                $innerHtml .= "</td></tr></table>";
            }
            if ($record->fields['status'] == $this->issued) {
                $innerHtml .= "<div style='margin-top:10px;'></div>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr><input id='id' value='" . $recordDisCard->fields['ID'] . "' name='id' type='hidden' size='40'/><input id='card' value='" . $cardId . "' name='card' type='hidden' size='40'/>";
                $innerHtml .= "<th width=370px> Employee Name</th><th width=100px> Card ID</th><th> Return Date<br/>(YYYY-MM-DD)</th><th>Used Period <br/> (YYYY-MM-DD)</th> </tr>";
                if ($recordDisCard->fields['employ_id'] == 0) {
                    $innerHtml .= "<td align=center>Background Card </td>";
                } else {
                    $innerHtml .= "<td> " . $recordDisCard->fields['full_name'] . "</td>";
                }
                $innerHtml .= "<td>" . $cardId . "</td><td><input onfocus='addDatePicker(this)' type='text' size='10' value='" . $recordDisCard->fields['received_date'] . "' id='returnDate' name='returnDate'></td><td>From:<input onfocus='addDatePicker(this)' type='text' size='10' value='" . $recordDisCard->fields['from_date'] . "' id='fromDate' name='fromDate'> To:<input onfocus='addDatePicker(this)' type='text' size='10' value='" . $recordDisCard->fields['to_date'] . "' id='toDate' name='toDate'></td></tr></table>";
                $innerHtml .= "<div style='margin-top:10px;'></div>";
                $innerHtml .= "<button id='btnLoad' onclick='collectCard();' privilege='' class='clsButtonSmall'>Accept Card</button>";
                $innerHtml .= "<button id='btnLoad' onclick='cancel();' privilege='' class='clsButtonSmall'>Cancel</button>";
                $innerHtml .= "<div style='margin-top:10px;'></div>";
            } else {
                if ($record->fields['status'] == $this->available) {
                    $innerHtml .= " <span style='background:#7cfc00;font-size:20px; margin-left:250px;'>Card status is Available</span>";
                } elseif ($record->fields['status'] == $this->processing) {
                    $innerHtml .= " <span style='background:#7878E6;font-size:20px; margin-left:250px; '>Card status is Processing</span>";
                } elseif ($record->fields['status'] == $this->invalid) {
                    $innerHtml .= " <span style='background:#ff0000;font-size:20px;margin-left:250px; '>Card status is Invalid</span>";
                } elseif ($record->fields['status'] == $this->not_Return) {
                    $innerHtml .= " <span style='background:#ff00ff;font-size:20px; margin-left:250px;'>Card status is Not Return</span>";
                }


            }


        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }

    public function getCardInfo($cardId) {

        $sql = "SELECT current_dis_list, aea_tld.status, lot_id,is_pass_test,is_qa_card,is_broken,lot_name,aea_tld_lot.type,material,specification,supplier,received_date
       FROM aea_tld
       JOIN aea_tld_lot ON aea_tld.lot_id=aea_tld_lot.id
       WHERE aea_tld.code ='$cardId' LIMIT 1";
        $record = $this->db->Execute($sql);
        $totalRows = $record->RecordCount();

        if ($totalRows == 0) {
            $innerHtml = "<span style='color:red;font-size:25px;'>No Record found !  Card ID " . $cardId . " is invalid!</span>";
        } else {

            $innerHtml .= "<div style='margin-top:20px;'></div>";
            $innerHtml .= "<table class='gradienttable'>";
            $innerHtml .= "<tr>";
            $innerHtml .= "<th colspan =4>Information of the card ID:" . $cardId . "  </th>";

            $innerHtml .= "</tr>";

            $innerHtml .= "<tr>";
            $innerHtml .= "<td width='100px'>Lot Name  </td>";
            $innerHtml .= "<td width='300px'> " . $record->fields['lot_name'] . "</td>";
            $innerHtml .= "<td width='170px'>Type of the material  </td>";
            $innerHtml .= "<td width='300px'> " . $record->fields['material'] . "</td>";
            $innerHtml .= "</tr>";

            $innerHtml .= "<tr>";
            $innerHtml .= "<td>Supplier  </td>";
            $innerHtml .= "<td> " . $record->fields['supplier'] . "</td>";
            $innerHtml .= "<td>Received Date  </td>";
            $innerHtml .= "<td> " . $record->fields['received_date'] . "</td>";
            $innerHtml .= "</tr>";

            $innerHtml .= "<tr>";
            $innerHtml .= "<td>Specification  </td>";
            $innerHtml .= "<td> " . $record->fields['specification'] . "</td>";
            $innerHtml .= "<td>Card Type  </td>";
            if ($record->fields['is_qa_card'] == 0) {
                $innerHtml .= "<td> User Card</td>";
            } else {
                $innerHtml .= "<td> Quality Assurance Card</td>";
            }
            $innerHtml .= "</tr>";


            $innerHtml .= "</table>";


            $currentDisId = $record->fields['current_dis_list'];
            $sql = "SELECT aea_distribution_card.id AS ID, dose, employ_id, received_date, from_date,to_date,aea_distribution_list.institute_id AS ins_id, aea_distribution_list.section_id AS sec_id,
                        month , year, to_month, to_year,send_date,full_name
                        FROM aea_distribution_card
                        JOIN aea_distribution_list ON aea_distribution_card.list_id = aea_distribution_list.id
                        LEFT JOIN aea_profile ON aea_distribution_card.employ_id = aea_profile.id
                        WHERE list_id ='$currentDisId'
                        AND card_id ='$cardId'
                        LIMIT 1";
            $recordDisCard = $this->db->Execute($sql);
            $totalRows2 = $recordDisCard->RecordCount();
            if ($totalRows2 > 0) {

                $this->setInstituteArray();
                $innerHtml .= "<div style='margin-top:20px;'></div>";
                $innerHtml .= "<span style='background:#00008b;font-size:20px;color:aqua;'>Last Record Related this card ID:" . $cardId . "</span>";
                $innerHtml .= "<div style='margin-top:10px;'></div>";
                $innerHtml .= "<table border='0'><tr><td width=500px>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr>";
                if ($recordDisCard->fields['employ_id'] == 0) {
                    $innerHtml .= "<td colspan=2 align='center'>Background Card :" . $cardId . "</td>";

                } else {
                    $innerHtml .= "<td width=150px>Employee Name  </td>";
                    $innerHtml .= "<td width=350px> " . $recordDisCard->fields['full_name'] . "</td>";
                }
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td td width=150px>Institute Name  </td>";
                $innerHtml .= "<td td width=350px> " . $this->instituteMenu[$recordDisCard->fields['ins_id']] . "</td>";
                $innerHtml .= "</tr>";
                if ($recordDisCard->fields['sec_id'] != 0) {
                    $innerHtml .= "<tr>";
                    $innerHtml .= "<td>Section Name  </td>";
                    $innerHtml .= "<td> " . $this->instituteMenu[$recordDisCard->fields['sec_id']] . "</td>";
                    $innerHtml .= "</tr>";
                }
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>Sent Date  </td>";
                $innerHtml .= "<td> " . $recordDisCard->fields['send_date'] . "</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";

                $innerHtml .= "</td><td>";
                $innerHtml .= "<table class='gradienttable'>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<th colspan=2>Period  </th>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td width=100px>From  </td>";
                $innerHtml .= "<td width=200px>  " . $this->months[$recordDisCard->fields['month']] . " " . $recordDisCard->fields['year'] . " (" . $recordDisCard->fields['from_date'] . ")</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>To  </td>";
                $innerHtml .= "<td>  " . $this->months[$recordDisCard->fields['to_month']] . " " . $recordDisCard->fields['to_year'] . " (" . $recordDisCard->fields['to_date'] . ")</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "<tr>";
                $innerHtml .= "<td>Received Date  </td>";
                $innerHtml .= "<td>  " . $recordDisCard->fields['received_date'] . "</td>";
                $innerHtml .= "</tr>";
                $innerHtml .= "</table>";
                $innerHtml .= "</td></tr></table>";
            }
            $innerHtml .= "<div style='margin-top:10px;'></div>";
            if ($record->fields['status'] == $this->processing) {
                $innerHtml .= " <span style='color:#7878E6;font-size:20px;margin-left:250px '>Card status is Processing</span>";
            } else {
                if ($record->fields['status'] == $this->available) {
                    $innerHtml .= " <span style='color:#7cfc00;font-size:20px; margin-left:250px;'>Card status is Available</span>";
                } elseif ($record->fields['status'] == $this->issued) {
                    $innerHtml .= " <span style='color:#FFFF00;font-size:20px; margin-left:250px; '>Card status is Issued</span>";
                } elseif ($record->fields['status'] == $this->invalid) {
                    $innerHtml .= " <span style='color:#ff0000;font-size:20px; margin-left:250px; '>Card status is Invalid</span>";
                } elseif ($record->fields['status'] == $this->not_Return) {
                    $innerHtml .= " <span style='color:#ff00ff;font-size:20px; margin-left:250px; '>Card status is Not Return</span>";
                }


            }


        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }

    public function getDistributionCollection($insId, $secId, $fromYear, $fromMonth, $toYear, $toMonth) {
        $sql = " SELECT aea_distribution_card.id AS ID,`employ_id`,`card_id`,from_date,to_date,`received_date`,full_name FROM `aea_distribution_card`
                    LEFT JOIN aea_profile on aea_distribution_card.employ_id = aea_profile.id
                    WHERE `list_id`=(SELECT id from aea_distribution_list
                                        WHERE  institute_id='$insId'
                                        AND section_id='$secId'
                                        AND month='$fromMonth'
                                        AND year='$fromYear'
                                        AND to_month='$toMonth'
                                        AND to_year='$toYear')";
        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $count = 1;
            $innerHtml = "<table class='gradienttable'><tr><th width='30px'></th><th width='350px'>Employee Name</th><th width='100px'>Card ID</th><th width='100px'>Returned Date</br> ( YYYY-MM-DD )</th>  <th width='300px'>Used Period</br> ( YYYY-MM-DD )</th><th width='50px'>NR</th></tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['ID'] . "' />
                    <input type='hidden' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'>
                    <td>" . $count . "</td>";

                if ($resAll->fields['employ_id'] == 0) {
                    $innerHtml .= "<td>Background Card</td>";
                } else {
                    $innerHtml .= "<td>" . $resAll->fields['full_name'] . "</td>";
                }

                $innerHtml .= "<td>" . $resAll->fields['card_id'] . "</td>";
                $innerHtml .= "<td align='center'><input onfocus='addDatePicker(this) type='text' size='10' value='" . $resAll->fields['received_date'] . "' id='returnDate" . $resAll->fields['ID'] . "' name='returnDate[" . $resAll->fields['ID'] . "]'></td>";
                $innerHtml .= "<td align='center'>From:<input type='text' onfocus='addDatePicker(this)'  size='10' value='" . $resAll->fields['from_date'] . "' id='fromDate" . $resAll->fields['ID'] . "' name='fromDate[" . $resAll->fields['ID'] . "]'> To:<input type='text' onfocus='addDatePicker(this)'  size='10'  value='" . $resAll->fields['to_date'] . "' id='toDate" . $resAll->fields['ID'] . "' name='toDate[" . $resAll->fields['ID'] . "]'></td>";
                $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td></tr>";
                $count++;
                $resAll->MoveNext();
            }


            $innerHtml .= "</table> <input type='hidden' id='total'  name='total' value='" . $totalRows . "' />";

        } else {
            $innerHtml = "<span style='color:red;font-size:25px;'>No Records Found on Selected Period !</span>";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;

    }

    public function getDistributionCollectionWithDose($insId, $secId, $fromYear, $fromMonth, $toYear, $toMonth) {
        $sql = " SELECT aea_distribution_card.id AS ID,`employ_id`,`card_id`,`received_date`,from_date,to_date,full_name,dose FROM `aea_distribution_card`
                    LEFT JOIN aea_profile on aea_distribution_card.employ_id = aea_profile.id
                    WHERE  aea_distribution_card.status ='$this->processing' AND `list_id`=(SELECT id from aea_distribution_list
                                        WHERE  institute_id='$insId'
                                        AND section_id='$secId'
                                        AND month='$fromMonth'
                                        AND year='$fromYear'
                                        AND to_month='$toMonth'
                                        AND to_year='$toYear')";
        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $count = 1;
            $innerHtml = "<table class='gradienttable'><tr><th width='30px'></th><th width='350px'>Employee Name</th><th width='100px'>Card ID</th><th width='100px'>Returned Date</th><th width='300px'>Used Period</br> ( YYYY-MM-DD )</th><th width='80px'>Dose (&micro;Sv)</th> <th width='50px'>Skip</th> </tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['ID'] . "' />
                    <input type='hidden' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'>
                    <td>" . $count . "</td>";

                if ($resAll->fields['employ_id'] == 0) {
                    $innerHtml .= "<td>Background Card</td>";
                } else {
                    $innerHtml .= "<td>" . $resAll->fields['full_name'] . "</td>";
                }

                $innerHtml .= "<td>" . $resAll->fields['card_id'] . "</td>";
                $innerHtml .= "<td align='center'>" . $resAll->fields['received_date'] . "</td>";
                $innerHtml .= "<td align='center'>From:<input type='text' onfocus='addDatePicker(this)'  size='10' value='" . $resAll->fields['from_date'] . "' id='fromDate" . $resAll->fields['ID'] . "' name='fromDate[" . $resAll->fields['ID'] . "]'> To:<input type='text' onfocus='addDatePicker(this)'  size='10'  value='" . $resAll->fields['to_date'] . "' id='toDate" . $resAll->fields['ID'] . "' name='toDate[" . $resAll->fields['ID'] . "]'></td>";
                $innerHtml .= "<td align='center'><input type='text' onblur='validateDoseValue(this)' value='" . $resAll->fields['dose'] . "' id='dose" . $resAll->fields['ID'] . "' name='dose[" . $resAll->fields['ID'] . "]'></td>";
                $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='skip" . $resAll->fields['ID'] . "' name='skip[" . $resAll->fields['ID'] . "]' /> </td></tr>";
                $count++;
                $resAll->MoveNext();
            }


            $innerHtml .= "</table> <input type='hidden' id='total'  name='total' value='" . $totalRows . "' />";

        } else {
            $innerHtml = "<span style='color:red;font-size:25px;'>No Records Found on Selected Period !</span>";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;

    }

    public function getDistributionCollectionWithDoseAdmin($insId, $secId, $fromYear, $fromMonth, $toYear, $toMonth) {
        $sql = " SELECT aea_distribution_card.id AS ID,`employ_id`,`card_id`,`received_date`,from_date,to_date,full_name,dose FROM `aea_distribution_card`
                    LEFT JOIN aea_profile on aea_distribution_card.employ_id = aea_profile.id
                    WHERE `list_id`=(SELECT id from aea_distribution_list
                                        WHERE  institute_id='$insId'
                                        AND section_id='$secId'
                                        AND month='$fromMonth'
                                        AND year='$fromYear'
                                        AND to_month='$toMonth'
                                        AND to_year='$toYear')";
        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $count = 1;
            $innerHtml = "<table class='gradienttable'><tr><th width='30px'></th><th width='350px'>Employee Name</th><th width='100px'>Card ID</th><th width='100px'>Returned Date</th><th width='300px'>Used Period</br> ( YYYY-MM-DD )</th><th width='80px'>Dose (&micro;Sv)</th> <th width='50px'>Skip</th> </tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['ID'] . "' />
                    <input type='hidden' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'>
                    <td>" . $count . "</td>";

                if ($resAll->fields['employ_id'] == 0) {
                    $innerHtml .= "<td>Background Card</td>";
                } else {
                    $innerHtml .= "<td>" . $resAll->fields['full_name'] . "</td>";
                }

                $innerHtml .= "<td>" . $resAll->fields['card_id'] . "</td>";
                $innerHtml .= "<td align='center'>" . $resAll->fields['received_date'] . "</td>";
                $innerHtml .= "<td align='center'>From:<input type='text' onfocus='addDatePicker(this)'  size='10' value='" . $resAll->fields['from_date'] . "' id='fromDate" . $resAll->fields['ID'] . "' name='fromDate[" . $resAll->fields['ID'] . "]'> To:<input type='text' onfocus='addDatePicker(this)'  size='10'  value='" . $resAll->fields['to_date'] . "' id='toDate" . $resAll->fields['ID'] . "' name='toDate[" . $resAll->fields['ID'] . "]'></td>";
                $innerHtml .= "<td align='center'><input type='text' onblur='validateDoseValue(this)' value='" . $resAll->fields['dose'] . "' id='dose" . $resAll->fields['ID'] . "' name='dose[" . $resAll->fields['ID'] . "]'></td>";
                $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='skip" . $resAll->fields['ID'] . "' name='skip[" . $resAll->fields['ID'] . "]' /> </td></tr>";
                $count++;
                $resAll->MoveNext();
            }


            $innerHtml .= "</table> <input type='hidden' id='total'  name='total' value='" . $totalRows . "' />";

        } else {
            $innerHtml = "<span style='color:red;font-size:25px;'>No Records Found on Selected Period !</span>";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;

    }

    public function getEmpTldCardListDistribution($instituteId, $sectionId, $list) {
        if ($list == 1) {
            $sql = "SELECT  aea_tld.status AS ST, aea_employ_institute.institute_id AS int_id,aea_employ_institute.section_id AS sec_id,aea_employ_institute.emp_id AS ID,full_name,aea_profile.institute_id,list1_card_id AS card_id
                      FROM  aea_employ_institute LEFT JOIN aea_tld
                      ON aea_employ_institute.list1_card_id=aea_tld.code
                      JOIN  aea_profile ON aea_profile.id=aea_employ_institute.emp_id 
                      AND aea_profile.is_delete='0'
                      AND aea_employ_institute.to_date='0000-00-00'";
            //and aea_employ_institute.list1_card_id==aea_tld.code ;
        } else {
            $sql = "SELECT  aea_tld.status AS ST, aea_employ_institute.institute_id AS int_id,aea_employ_institute.section_id AS sec_id,aea_employ_institute.emp_id AS ID,full_name,aea_profile.institute_id,list2_card_id AS card_id
                      FROM  aea_employ_institute LEFT JOIN aea_tld
                      ON aea_employ_institute.list2_card_id=aea_tld.code
                      JOIN  aea_profile ON aea_profile.id=aea_employ_institute.emp_id
                      AND aea_profile.is_delete='0'
                      AND aea_employ_institute.to_date='0000-00-00'";


            //"SELECT  aea_employ_institute.institute_id as int_id,aea_employ_institute.section_id as sec_id,aea_employ_institute.emp_id as ID,full_name,aea_profile.institute_id,list2_card_id as card_id FROM aea_employ_institute, aea_profile Where aea_profile.id=aea_employ_institute.emp_id AND aea_profile.is_delete='0' and to_date='0000-00-00'" ;//and aea_employ_institute.list2_card_id==aea_tld.code;
        }

        if ($instituteId > 0)
            $sql .= " and aea_employ_institute.institute_id = '$instituteId'";
        if ($sectionId > 0)
            $sql .= " and aea_employ_institute.section_id = '$sectionId'";

        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $innerHtml = "<table class='gradienttable'><tr><th width='50px'>ID</th><th width='450px'>Employee Name</th><th width='150px'>Card ID</th> <th width='50px'>NR</th><th width='50px'>Skip</th></tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['ID'] . "' />
                                      <input type='hidden' id='intId" . $resAll->fields['ID'] . "'  name='intId[" . $resAll->fields['ID'] . "]' value='" . $resAll->fields['int_id'] . "' />
                                      <input type='hidden' id='secId" . $resAll->fields['ID'] . "'  name='secId[" . $resAll->fields['ID'] . "]' value='" . $resAll->fields['sec_id'] . "' />
                    <td>" . $resAll->fields['ID'] . "</td>
                   <td>" . $resAll->fields['full_name'] . "</td>";

                if ($resAll->fields['ST'] == CardStatus::AVAILABLE) {
                    // valid tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#7cfc00;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::NOT_RETURNED) {
                    // not return TLD card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#ff00ff;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' checked value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::ISSUED) {
                    // issued tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#FFFF00;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::PROCESSING) {
                    // processing tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#7878E6;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::INVALID) {
                    // invalid tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#ff0000;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                }


                $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='rm" . $resAll->fields['ID'] . "' name='rm[" . $resAll->fields['ID'] . "]' /> </td>
                  </tr>";
                $resAll->MoveNext();
            }


            if ($sectionId != 0) {
                if ($list == 1) {
                    $sql = "SELECT  bg_card1 AS bg FROM aea_institute  where id= '$sectionId'";
                } else {
                    $sql = "SELECT  bg_card2 AS bg FROM aea_institute  where id= '$sectionId'";
                }
            } else {
                if ($list == 1) {
                    $sql = "SELECT  bg_card1 AS bg FROM aea_institute  where id= '$instituteId'";
                } else {
                    $sql = "SELECT  bg_card2 AS bg FROM aea_institute  where id= '$instituteId'";
                }
            }
            $recode = $this->db->Execute($sql);
            $rows = $recode->RecordCount();
            if ($rows > 0) {
                while (!$recode->EOF) {
                    $innerHtml .= "<tr>
                                    <td> </td>
                                    <td>Background Card</td>
                                    <td align='center'><input type='text' onblur='validateCode(this)' value='" . $recode->fields['bg'] . "' id='bgCard' name='bgCard'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nrbk' name='nrbk' /> </td>";
                    $innerHtml .= "</tr>";
                    $recode->MoveNext();
                }

            }

            $innerHtml .= "</table> <input type='hidden' id='total'  name='total' value='" . $totalRows . "' />";

        } else {
            $innerHtml = "No Records Found !";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }

    public function getEmpTldCardListDistributionMore($instituteId, $sectionId, $list, $fromMonth, $fromYear, $toMonth, $toYear) {
        $sql = "SELECT id FROM aea_distribution_list
                 WHERE institute_id='$instituteId' AND section_id = '$sectionId' AND month = '$fromMonth' AND year = '$fromYear' AND to_month = '$toMonth' AND to_year = '$toYear' LIMIT 1";
        $recordId = $this->db->Execute($sql);
        $listId = $recordId->fields['id'];

        if ($list == 1) {
            $sql = "SELECT  aea_tld.status AS ST, aea_employ_institute.institute_id AS int_id,aea_employ_institute.section_id AS sec_id,aea_employ_institute.emp_id AS ID,full_name,aea_profile.institute_id,list1_card_id AS card_id
                      FROM  aea_employ_institute LEFT JOIN aea_tld
                      ON aea_employ_institute.list1_card_id=aea_tld.code
                      JOIN  aea_profile ON aea_profile.id=aea_employ_institute.emp_id
                      AND aea_profile.is_delete='0'
                      AND aea_employ_institute.to_date='0000-00-00'";
            //and aea_employ_institute.list1_card_id==aea_tld.code ;
        } else {
            $sql = "SELECT  aea_tld.status AS ST, aea_employ_institute.institute_id AS int_id,aea_employ_institute.section_id AS sec_id,aea_employ_institute.emp_id AS ID,full_name,aea_profile.institute_id,list2_card_id AS card_id
                      FROM  aea_employ_institute LEFT JOIN aea_tld
                      ON aea_employ_institute.list2_card_id=aea_tld.code
                      JOIN  aea_profile ON aea_profile.id=aea_employ_institute.emp_id
                      AND aea_profile.is_delete='0'
                      AND aea_employ_institute.to_date='0000-00-00'";


            //"SELECT  aea_employ_institute.institute_id as int_id,aea_employ_institute.section_id as sec_id,aea_employ_institute.emp_id as ID,full_name,aea_profile.institute_id,list2_card_id as card_id FROM aea_employ_institute, aea_profile Where aea_profile.id=aea_employ_institute.emp_id AND aea_profile.is_delete='0' and to_date='0000-00-00'" ;//and aea_employ_institute.list2_card_id==aea_tld.code;
        }

        if ($instituteId > 0)
            $sql .= " and aea_employ_institute.institute_id = '$instituteId'";
        if ($sectionId > 0)
            $sql .= " and aea_employ_institute.section_id = '$sectionId'";

        $sql .= "AND aea_employ_institute.emp_id NOT IN (SELECT employ_id from aea_distribution_card WHERE list_id ='$listId') ";
        $resAll = $this->db->Execute($sql);
        $totalRows = $resAll->RecordCount();
        if ($totalRows > 0) {
            $innerHtml = "<table class='gradienttable'><tr><th width='50px'>ID</th><th width='450px'>Employee Name</th><th width='150px'>Card ID</th> <th width='50px'>NR</th><th width='50px'>Skip</th></tr>";
            while (!$resAll->EOF) {

                $innerHtml .= "<tr> <input type='hidden' id='id'  name='id[]' value='" . $resAll->fields['ID'] . "' />
                                      <input type='hidden' id='intId" . $resAll->fields['ID'] . "'  name='intId[" . $resAll->fields['ID'] . "]' value='" . $resAll->fields['int_id'] . "' />
                                      <input type='hidden' id='secId" . $resAll->fields['ID'] . "'  name='secId[" . $resAll->fields['ID'] . "]' value='" . $resAll->fields['sec_id'] . "' />
                    <td>" . $resAll->fields['ID'] . "</td>
                   <td>" . $resAll->fields['full_name'] . "</td>";

                if ($resAll->fields['ST'] == CardStatus::AVAILABLE) {
                    // valid tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#7cfc00;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::NOT_RETURNED) {
                    // not return TLD card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#ff00ff;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' checked value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::ISSUED) {
                    // issued tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#FFFF00;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::PROCESSING) {
                    // processing tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#7878E6;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                } elseif ($resAll->fields['ST'] == CardStatus::INVALID) {
                    // invalid tld card
                    $innerHtml .= "<td align='center'><input type='text' style='background:#ff0000;' onblur='validateCode(this)' value='" . $resAll->fields['card_id'] . "' id='card" . $resAll->fields['ID'] . "' name='card[" . $resAll->fields['ID'] . "]'></td>";
                    $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='nr" . $resAll->fields['ID'] . "' name='nr[" . $resAll->fields['ID'] . "]' /> </td>";
                }


                $innerHtml .= "<td align='center'><input type='checkbox' value='1' id='rm" . $resAll->fields['ID'] . "' name='rm[" . $resAll->fields['ID'] . "]' /> </td>
                  </tr>";
                $resAll->MoveNext();
            }


            $innerHtml .= "</table> <input type='hidden' id='listId'  name='listId' value='" . $listId . "' />";

        } else {
            $innerHtml = "No Records Found !";
        }
        $data = array('tableHtml' => $innerHtml);
        return $data;
    }
}

require_once('libraries/adodb5/adodb-active-record.inc.php');
class TldCardAR extends ADOdb_Active_Record {
    var $_table = 'aea_tld';

}

class EmpCardListAR extends ADOdb_Active_Record {
    var $_table = 'aea_employ_institute';

}

class DistributionListAR extends ADOdb_Active_Record {
    var $_table = 'aea_distribution_list';

}

class DistributionCardAR extends ADOdb_Active_Record {
    var $_table = 'aea_distribution_card';

}